from spass.core.error import error
from spass.core.message import message
from spass.core.db.spassobj import *
from spass.core.warehouse import (core_w_create_template,
                                  core_w_create_assignment)
import os, re, env

def core_del_teamset(id_team_set):
    count = env.session.query(TeamSet).filter(TeamSet.id_team_set == id_team_set).count()
    if not count:
        error('teamset.notfound', [str(id_team_set)])
        return 1
    
    env.session.query(Assignment).filter(Assignment.id_teamset == id_team_set).delete()
    env.session.query(TeamSet).filter(TeamSet.id_team_set == id_team_set).delete()
    env.session.commit()

def core_del_team(id_team):
    count = env.session.query(Team).filter(Team.id_team == id_team).count()
    if not count:
        error('team.notfound', [str(id_team)])
        return 1

    env.session.query(Team).filter(Team.id_team == id_team).delete()
    teamset = env.session.query(TeamSet).filter(TeamSet.id_team == id_team).first()
    tid = teamset.id_team_set
    core_del_teamset(tid)
    env.session.commit()

def core_get_users_list():
    return env.session.query(User).all()

def core_add_user(fullname, name):
    count = env.session.query(User).filter(User.fullname == fullname).filter(User.name == name).count()
    if count:
        error('already.exists', ["User", fullname + ' ' + name])
        return 1

    env.session.add(User(fullname, name))
    env.session.commit()
    message('successfully.added', ["User "+fullname +' '+name, ''])
    return 0

def core_del_user(fullname, name):
    count = env.session.query(User).filter(User.fullname == fullname).filter(User.name == name).count()
    if not count:
        error('user.notfound', [fullname + " " +name])
        return 1

    user = env.session.query(User).filter(User.fullname == fullname).filter(User.name == name).first()
    uid = user.id
    count = env.session.query(Assignment).filter(Assignment.id_user == uid).count()
    if count > 0:
        error('supervisor.of', [fullname +' '+ name, ''])
        return 1

    env.session.query(User).filter(User.fullname == fullname).filter(User.name == name).delete()

    #case where the deleted user was the last one from a team
    #in this case, we must also delete the team which is actually empty
    teams = env.session.query(Team).filter(Team.id_user == uid).all()
    for team in teams:
        count = env.session.query(Team).filter(Team.id_team == team.id_team).count()
        if count == 1:
            core_del_team(team.id_team)
    
    env.session.commit()
    message('successfully.removed', ["User "+fullname +' '+name, ''])
    return 0

def core_add_user_to_team(id_team, fullname, name):
    user = None
    count = env.session.query(User).filter(User.fullname == fullname).filter(User.name == name).count()
    if not count:
        error('user.notfound', [fullname+" "+name])
        return 1
    else:
        user = env.session.query(User).filter(User.fullname == fullname).filter(User.name == name).first()
        count = env.session.query(Team).filter(Team.id_team == id_team).filter(Team.id_user == user.id).count()
        if count > 0:
            error('already.into', ["User " + fullname + ' ' + name, 'team ' + str(id_team)])
            return 1

    uid = user.id
    env.session.add(Team(id_team, uid))
    env.session.commit()
    message('successfully.added.to', ["User "+fullname+" "+name, "Team "+str(id_team)])
    return 0

def core_del_user_from_team(id_team, fullname, name):
    count = env.session.query(User).filter(User.fullname == fullname).filter(User.name == name).count()
    if not count:
        error('user.notfound', [fullname + ' ' + name])
        return 1

    count = env.session.query(Team).filter(Team.id_team == id_team).count()
    if not count:
        error('team.notfound', [str(id_team)])
        return 1

    user = env.session.query(User).filter(User.fullname == fullname).filter(User.name == name).first()
    uid = user.id
    count = env.session.query(Team).filter(Team.id_team == id_team).filter(Team.id_user == uid).count()
    if not count:
        error('not.into', ["User " + fullname + ' ' + name, "Team " + str(id_team)])
        return 1

    env.session.query(Team).filter(Team.id_team == id_team).filter(Team.id_user == uid).delete()
    env.session.commit()
    message('successfully.removed.from', ["User "+fullname+" "+name, "Team "+str(id_team)])
    return 0

def core_get_teams_list():
    teams = env.session.query(Team).all()
    id_team = []
    for team in teams:
        id_team.append(team.id_team)

    #Delete duplicates in team's table
    for i in id_team:
        while id_team.count(i) > 1:
            id_team.remove(i)
            teams.pop(id_team.index(i))

    return teams

def core_get_users_from_team_list(id_team):
    users = []
    query = env.session.query(Team).filter(Team.id_team == id_team).all()
    for user in query:
        users.append(env.session.query(User).filter(User.id == user.id_user).first())
    return users

def core_add_team_to_teamset(id_team_set, id_team):
    team = None
    count = env.session.query(Team).filter(Team.id_team == id_team).count()
    if not count:
        error('team.notfound', [str(id_team)])
        return 1
    else:
        team = env.session.query(Team).filter(Team.id_team == id_team).first()
        count = env.session.query(TeamSet).filter(TeamSet.id_team_set == id_team_set).filter(TeamSet.id_team == id_team).count()
        if count > 0:
            error('already.into', ["Team "+str(id_team), "TeamSet "+ str(id_team_set)])
            return 1

    env.session.add(TeamSet(id_team_set, id_team))
    env.session.commit()
    message('successfully.added.to', ["Team "+str(id_team), "TeamSet "+str(id_team_set)])
    return 0

def core_del_team_from_teamset(id_team_set, id_team):
    count = env.session.query(Team).filter(Team.id_team == id_team).count()
    if not count:
        error('team.notfound', [str(id_team)])
        return 1

    count = env.session.query(TeamSet).filter(TeamSet.id_team_set == id_team_set).count()
    if not count:
        error('teamset.notfound', [str(id_team_set)])
        return 1
    
    count = env.session.query(TeamSet).filter(TeamSet.id_team_set == id_team_set).filter(TeamSet.id_team == id_team).count()
    if not count:
        error('not.into', ["Team "+str(id_team), "Teamset "+str(id_team_set)])
        return 1
    
    teamsets = env.session.query(TeamSet).filter(TeamSet.id_team == id_team).all()
    for teamset in teamsets:
        count = env.session.query(TeamSet).filter(TeamSet.id_team_set == teamset.id_team_set).count()
        if count == 1:
            core_del_teamset(teamset.id_team_set)
        else:
            env.session.query(TeamSet).filter(TeamSet.id_team_set == id_team_set).filter(TeamSet.id_team == id_team).delete()
            
    env.session.commit()
    message('successfully.removed.from', ["Team "+str(id_team), "TeamSet "+str(id_team_set)])
    return 0

def core_get_teamsets_list():
    teamsets = env.session.query(TeamSet).all()
    id_teamset = []
    for teamset in teamsets:
        id_teamset.append(teamset.id_team_set)
        
    #Delete duplicates from teamset's table
    for i in id_teamset:
        while id_teamset.count(i) > 1:
            id_teamset.remove(i)
            teamsets.pop(id_teamset.index(i))
    return teamsets


def core_get_teams_from_teamset_list(id_team_set):
    teams = []
    query = env.session.query(TeamSet).filter(TeamSet.id_team_set == id_team_set).all()
    for team in query:
        teams.append(env.session.query(Team).filter(Team.id_team == team.id_team).first())
    return teams


def core_new_template(title, test_path):
    if not os.path.exists(test_path):
        error("not.find", ["the path \""+test_path+"\""])
        return 1

    template = Template(title)
    env.session.add(template)
    env.session.commit()
    core_w_create_template(str(template.id), test_path)
    message('successfully.added', ["Template", ''])

def core_get_templates_list():
    return env.session.query(Template).all()

def core_create_assignment(id_template, date, id_team_set, id_user):
    count = env.session.query(Template).filter(Template.id == id_template).first()
    if not count:
        error('template.notfound', [str(id_template)])
        return 1
    count = env.session.query(User).filter(User.id == id_user).first()
    if not count:
        error('user.notfound', ["with id "+str(id_user)])
        return 1    
    count = env.session.query(TeamSet).filter(TeamSet.id_team_set == id_team_set).first()
    if not count:
        error('teamset.notfound', [str(id_team_set)])
        return 1  
    
    template = env.session.query(Template).filter(Template.id == id_template).first()
    assignment = Assignment(id_template, date, id_team_set, id_user)
    env.session.add(assignment)
    env.session.commit()
    core_w_create_assignment(str(id_template), str(assignment.id))
    message('successfully.added', ["Assignment", ''])    
    return 0

def core_get_assignments_list():
    return env.session.query(Assignment).all()

def core_info_user(fullname, name):
    count = env.session.query(User).filter(User.fullname == fullname).filter(User.name == name).count()
    if not count:
        error('user.notfound', [fullname + ' '+ name])
        return 1
    
    user = env.session.query(User).filter(User.fullname == fullname).filter(User.name == name).first()
    uid = user.id
    ass_all = [] # o_0
    teams = env.session.query(Team).filter(Team.id_user == uid).all()
    for team in teams:
        teamsets = env.session.query(TeamSet).filter(TeamSet.id_team == team.id_team).all()
        for teamset in teamsets:
            asss = env.session.query(Assignment).filter(Assignment.id_teamset == teamset.id_team_set).all()
            for ass in asss:
                ass_all.append(ass)

    result = []
    result.append(teams)
    if not ass_all == []:
        if ass_all[0] == None:
            ass_all[0].pop()
    result.append(ass_all)
    return result

def core_info_team(id_team):    
    count = env.session.query(Team).filter(Team.id_team == id_team).count()
    if not count:
        error('team.notfound', [str(id_team)])
        return 1
    
    ass_all = []
    teamsets = env.session.query(TeamSet).filter(TeamSet.id_team == id_team).all()
    for teamset in teamsets:
        asss = env.session.query(Assignment).filter(Assignment.id_teamset == teamset.id_team_set).all()
        for ass in asss:
            ass_all.append(ass)

    result = []
    result.append(teamsets)
    if not ass_all == []:
        if ass_all[0] == None:
            ass_all.pop()
    result.append(ass_all)
    return result

def core_info_teamset(id_teamset):
    count = env.session.query(TeamSet).filter(TeamSet.id_team_set == id_teamset).count()
    if not count:
        error('teamset.notfound', [str(id_teamset)])
        return 1
    
    ass = env.session.query(Assignment).filter(Assignment.id_teamset == id_teamset).all()
    return ass

def core_search_user(fullname_regex, name_regex):    
    users = core_get_users_list()
    users_found = []
    users_found_f = []
    users_found_n = []
    
    pfullname = re.compile(fullname_regex, re.I)
    pname = re.compile(name_regex, re.I)
    for user in users:
        fullname = pfullname.search(user.fullname)
        name = pname.search(user.name)
        if not fullname == None:
            users_found_f.append(user)
        if not name == None:
            users_found_n.append(user)

    for union in users_found_f:
        if users_found_n.count(union) > 0:
            users_found.append(union)
    return users_found
